Boolean queries

In This Topic

These queries are the result of requests from libraries to Spydus Support for reports which cannot be satisfied from either web Enquiry or from the Reports module.

For further information on creating Boolean searches, please refer to the Web Enquiry and Advanced Enquiry training notes.

Unless otherwise stated, these queries should be run in Enquiry (Staff - cat.html or WBA) from Search Holdings > Boolean Query .

Borrowers

Inactive borrowers

All Junior borrowers who have not borrowed an item or placed a reservation since 30 JUN 2005. Change dates and borrower category (BRWCATX) as required.

FORMAT: BRW - ((BRWLOAN> (ISSDTE: "> 30 JUN 2005") / BRWRSV> (RSVPDTE:"> 30 MAR 2005"))) + BDC< BRWCATX: J

Borrowers with notices sent via telephone and/or email

All borrowers with notices sent via telephone (1) or telephone and email (2). This query must be run with the first part in the Boolean query field and the second part in the SQL options field.

FORMAT: BRWD

JOIN QNT ON #TBLID.IRN = QNT.IRN WHERE TEL = 1 OR TEL = 2

Borrower gender identity

This query must be run with the first part in the Boolean query field and the second part in the SQL options field.

BRWSEX: X

The value of X in the SQL section must be a value present in the NPDS lookup table (which can be managed via Table Manager).

Translate between BRW and BRWD records

To use the bulk change facility, you may need to translate from the BRW format to BRWD. The following query selects borrowers in a particular borrower category (“A” in this example) and translates them to a set of BRWD records for updating, e.g. changing residential area code.

BRWDBRW> (BDC< BRWCATX: A)

To translate BRWD records to BRW, use the less than operator (<) on the BRWDBRW term instead or the greater than operator. The following query selects all borrower details records with email addresses (BDE: *) and translates them to BRW records.

BRWDBRW< (BDE: *)

Items

Low use items

Low-use items report. This query must be run with the first part in the Boolean query field and the second part in the SQL options field.

Change the item acquired date (ITD.DTE) and loan count (in this case 3) as required.

FORMAT:ITM

JOIN ITD ON #TBLID.IRN = ITD.IRN LEFT OUTER JOIN OND ON ITD.IRN = OND.ITM WHERE ITD.DTE < '1 JUL 2016' GROUP BY #TBLID.IRN HAVING COUNT(OND.IRN) < 3

Loans with resolved claims returned

Loans with Claims Returned status resolved by borrower (change to \L for resolved by library) within a particular date range.

LOANSDTE: "01 JUL 2015 - 30 JUN 2017" + CRR01\B

Lost items that have been paid for

Lost items that have been paid for.

LOANITM> (LOANCHRG< (CHRGCDE: L + CHRGCF: 1) + LOANSCDE: L)

Items with specific GMD

Items with a specific GMD. Change "DVD" to the GMD required.

BIBITM< GMD:DVD

Items never borrowed

Here are three approaches. This first approach includes bibliographic records where other copies have been loaned.

Do an Item Search and specify the Collection and Date first available (e.g. < 1 JUL 2017).

Then click Refine Set, select the Not the following option, and specify the Boolean query:

LOANITM> (FORMAT: LOAN)

A second approach might be as follow. This excludes bibliographic records where other copies have been loaned.

Do an Item Search and specify the Collection and Date first available (e.g. < 1 JUL 2017).

Then click Translate Set, select Bibliographic records linked to these items, and click Translate Set.

Then click Refine Set, select the NOT the following option, and specify the Boolean Query:

BIBLOAN> (FORMAT: LOAN)

A third approach might be as follow. This takes a set of items in a given collection and added after a given date, and then takes from that set any items which have links to loans.

(ITMCOL< COLX: NF) + CD: "< 1 JAN 2017 - (LOANITM> FORMAT: LOAN)

Items with content in the Notes field

This query must be run with the first part in the Boolean query field and the second part in the SQL options field.

FORMAT: ITM

INNER JOIN ITD ON #TBLID.IRN = ITD.IRN WHERE (ITD.NTE IS NOT NULL)

Items with a status added on a specific date

As Item Status and Status Date are two separate fields, searching across both could return unintended results where an item has multiple statuses. e.g. if searching for status code ABC, and status date of 01-01-2020, the search will retrieve:

any items with status code ABC applied, and
any items with any status applied on 01/01/2020

While this set might include items with status code ABC applied on 01/01/2020, it would also include items that have status ABC applied at any time, and any other status applied on 01/01/2020. To search for items with a selected status applied on a selected date, use the following (substituting the status code and date):

Boolean: FORMAT: ITM

SQL statement: JOIN IST ON IST.IRN = #TBLID.IRN WHERE CDE = 'ABC' AND DTESET = '01 JAN 2020'

Loans

Items on loan on a given day

Items on loan on a given day for a given location. Change the dates and location code.

(ISSDTE: "< 2 MAR 2017") + (LOANCF: 0 / RTNDTE: "> 1 MAR 2015") + LOANILOC< LOCX: ABC

The location will be the issuing location and may be a Circulation line rather than a branch.

Claims Returned

Claims Returned lodged this year

This is complex because when the CR is removed, the data is moved to the loan status history table, which is not indexed.

To get a list (and count) of CR set this year whether complete or not, you need to combine the Boolean Query with an SQL query:

FORMAT: LOAN

JOIN ONS ON #TBLID.IRN = ONS.IRN JOIN ONT ON #TBLID.IRN = ONT.IRN WHERE (ONS.CDE = 'CR' AND ONS.DTE > '01 JAN 2015') OR (ONT.CDE = 'CR' AND ONT.DTE > '01 JAN 2017')

Change the date as required. Please call the Service Desk if you need assistance with running this query.

If you are only interested in incomplete CR, then you can use just the following Boolean Query:

LOANSCDE: CR + LOANSDTE: "> 1 JAN 2017"

Resolution types

To get the number of resolved CR based on the resolution type use the query:

CRR01\B

JOIN ONT ON #TBLID.IRN = ONT.IRN WHERE ONT.DTE > '1 JAN 2017'

Change the code in the Boolean for the different types:

B = Found by Borrower

C = Charge for Replacement

L = Found by Library

S = Resolved (Stat. Dec.)

W = Write-off

Reservations

Lost item with reservations: 1

Lost items with reservations. This query will return reservation records.

BIBRSV< (BIBLOAN> LOANSCDE: L) + RSVCF: 0

Lost items with reservations: 2

Lost items with reservations. This query will return the item records.

LOANITM> (BIBLOAN< (BIBRSV> RSVCF: 0) + LOANSCDE: L)

Reserved items on loan

Reserved items on loan. This query will return reservation records.

BIBRSV< (BIBLOAN> LOANCF: 0) + RSVCF: 0

Active orders with reservations

Active orders with reservations. Returns reservation records.

BIBRSV< (BIBORD> (FORMAT: LI - LICD)) + RSVCF: 0

Reservations on titles with no holdings

Reservations on titles with no holdings. Note this query must be run in Cataloguing / Bibliographic Search / Boolean Query.

BIBRSV> (RSVCF: 0 + FILTER: 1) - MAJOR: 06601

Reservations for a specific collection

Reservations for a specific collection, excluding allocated reservations. Change "AF" to the appropriate collection code. Returns reservation records.

(BIBRSV< (BIBITM> (ITMCOL< XCODE: NF)) - RVC07\*)

“In process” items linked to bib records with reservations: 1

"In Process" items linked to bibliographic records with reservations. Returns item records.

BIBITM< (BIBRSV> (RSVCF: 0)) + ITMSCDE: IP

“In process” items linked to bib records with reservations: 2

"In Process" items linked to bibliographic records with reservations. Returns reservation records.

BIBRSV< (BIBITM> (ITMSCDE: IP)) + RSVCF: 0

Shelf check assigned items excluding those for borrowers in specific categories

In this example there are two borrower categories to exclude: A and HB.

RVS06\0 - BRWRSV< (BDC< (BRWCATX: A / BRWCATX: HB))

This takes all reservations with a shelf check completed flag of 0, and removes from that set any reservations for borrowers in the nominated categories.

Expired reservations at the current location

We added three “placeholders” in Spydus9. The three placeholders are #NAMEIRN#, #USERIRN# and #LOCIRN#. For example, use #LOCIRN# in your Boolean query and we’ll use the current location.

FORMAT: RSV + RSVCTYP: E + RSVTLOC: #LOCIRN#

This lists all the expired reservations where the pickup location is the same as the current location.

Bookings

Bookings placed at a specific location/branch

Bookings placed at one specific location/branch for a month. Change "ABC" to the branch code required and change the date range.

BKGBRW> (BKGITMMAT< (ITMLOC< (LOCX: ABC)) + BKGPDTE: "1 SEP 16 - 30 SEP 17")

Notices

Notice documents in specified period

Count number of notice documents sent in a specified period. NDDDELMTH can be ‘M’ for mail, ‘SMS’ for SMS or ‘TEL’ for telephone.

FORMAT: NDD + CD: "1 SEP 2016 - 30 SEP 2017" + NDDDELMTH: M

Notices generated on specified date

Check for notices generated on a specified date:

FORMAT: NTC + LUPD: "30 MAY 2017"

Notice documents generated on specified date

Check for notice documents generated on a specified date:

FORMAT: NDD + LUPD: "TODAY"

Notices of a specified type with a specified delivery method

Check for the long overdue notices (NTCCDE: LLO) delivered by e-mail (NDDDELMTH = ‘E’).

NTD03\> NTCCDE: O + NDDDELMTH: E

Change NTCCDE and NDDDELMTH as required. For example, NTCCDE might be O for overdue notices, V for reservation available notices or LL for lost notices. The notice codes used here are the same ones used in the notice policy. As well as ‘E’ for e-mail, NDDDELMTH can also be ‘M’ for mail, ‘SMS’ for SMS or ‘TEL’ for telephone.

You can use CD to search for notices created on a specific date or date range, for example:

NTD03\> NTCCDE: O + NDDDELMTH: E + CD: "TODAY"

Cataloguing

Spine label requested by specific user

Find all the spine labels queued for a specific user. Replace ABC with the user ID.

ISP04\< XCODE: ABC

Bibliographic records with no holdings

Here’s how to find the bibliographic records with active items:

BIBITM> (FORMAT: ITM - DYN: DEACTDTE)

If you subtract these records from the full set of bibliographic records then you’re left with the bibliographic records with no current items:

FORMAT: BIB - BIBITM> (FORMAT: ITM - DYN: DEACTDTE)

You could also subtract these records from the set of bibliographic records which had an item deactivated after a specified date. This gives you the bibliographic records with no current items where an item (presumably the last) was deactivated after the specified date (when the query was last run).

BIBITM> (ITMDDTE: "> 1 MAR 2017 ") - BIBITM> (FORMAT: ITM - DYN: DEACTDTE))

As this deals with deactivated / deleted items, it must either be run in Cataloguing / Bibliographic Search / Boolean Query, or in the regular Boolean Query page with the Include deleted records checkbox selected.

Requests

Bibliographic records with a current request

Find the bibliographic records with a current request:

BIBRQ> (QTP: * + (RQS: 18 / RQS: 20 / RQS: 22))

where RQS is the Request Status (see table PAP in Table Manager), QTP is the Request Type (see table PAT in Table Manager) and 05501 translates between the Request and Bibliographic formats.

Bibliographic records with a current interlibrary loan request

Find the bibliographic records with a current interlibrary loan request:

BIBRQ> ((QTP: QII / QTP: QIA) + (RQS: 18 / RQS: 20 / RQS: 22))

Serials

Retrieve all current reservations for a serial title

BIBRSV< (BIBISS< BRN: 1234) + RSVCF: 0 (change RSVCF: 0 to RSVCG: 1

for completed reservations, or remove for reservation count).

Retrieve all current loans for a serial title

BIBLOAN< (BIBISS< BRN: 1234) + LOANCF: 0

Retrieve all loans for past year for a serial title

BIBLOAN< (BIBISS< BRN: 1234) + ISSDTE: "TODAY(-365) - TODAY"

Retrieve all loans for given date range for a serial title

BIBLOAN< (BIBISS< BRN: 1234) + ISSDTE: "1 NOV 2016 - 31 OCT 2017

Replace 1234 with the BRN of the title.

Waive requests

Approved in last 30 days & Location Created = Current Branch

WVQSTS: A + WVQLOCX: $(CURRENT.BRANCH.CODE) + WVQPDTE:">= TODAY(-30)"

Pending for approval & Location Created = Current Branch

WVQSTS: P + WVQLOCX: $(CURRENT.BRANCH.CODE)

Declined in last 30 days & Location Created = Current Branch

WVQSTS: X + WVQLOCX: $(CURRENT.BRANCH.CODE) + WVQPDTE:">= TODAY(-30)"

Weeding lists

Processed in last 30 days & Location Created = Current Branch

WDQSTS: C + WDQLOCX: $(CURRENT.BRANCH.CODE) + WDQPDTE:">= TODAY(-30)"

Pending and Location Created = Current Branch & Last updated in last 60 days

WDQSTS: P + WDQLOCX: $(CURRENT.BRANCH.CODE) + LUPD:">= TODAY(-60)"

Declined & Location Created = Current Branch

WDQSTS: D + WDQLOCX: $(CURRENT.BRANCH.CODE)

Approval requested & Location Created = Current Branch

WDQSTS: Q + WDQLOCX: $(CURRENT.BRANCH.CODE)

SQL queries

SQL Enterprise Manager, SQL Query Analyser, SQL Management Studio, MS Access or similar can be used but care must be taken that no data is changed whilst using these tools.

Total cost of whole collection

Query for total cost of the whole collection. Change date or date range as required.

SELECT SUM(PRC) FROM ITD

INNER JOIN MAIN ON ITD.IRN = MAIN.IRN

WHERE MAIN.DEACT_DATE IS NULL

AND ITD.DTE < '1 NOV 2017'

Total cost of whole collection by institution

Query for total cost of each collection at 1 NOV 2017. Change date as required and replace XXX with your institution code. This query will total results by collection.

SELECT XCODE, SUM(PRC) FROM ITD

INNER JOIN INS ON ITD.IRN=INS.IRN

INNER JOIN ICO ON INS.IRN=ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

WHERE ITD.DTE < '1 NOV 2017'

AND MAIN.DEACT_DATE IS NULL

AND INS.CODE=’XXX’

GROUP BY MAIN.XCODE

Average cost of whole collection

Query for average cost of the whole collection. Change date or date range as required.

SELECT AVG(PRC) FROM ITD

INNER JOIN MAIN ON ITD.IRN = MAIN.IRN

WHERE MAIN.DEACT_DATE IS NULL

AND ITD.DTE < '1 NOV 2017'

Average cost of whole collection by institution

Query for average cost of the whole collection. Change date or date range as required and replace XXX with your institution code.

SELECT SUM(PRC) FROM ITD

INNER JOIN INS ON ITD.IRN=INS.IRN

INNER JOIN MAIN ON INS.IRN=MAIN.IRN

WHERE MAIN.DEACT_DATE IS NULL

AND INS.CODE='XXX'

AND ITD.DTE < '1 NOV 2017'

Total cost for each collection

Query for total cost of each collection at 1 NOV 2017. Change date as required. Will total results by collection.

SELECT XCODE, SUM(PRC) FROM ITD

INNER JOIN ICO ON ITD.IRN = ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

WHERE ITD.DTE < '1 NOV 2017'

AND MAIN.DEACT_DATE IS NULL

GROUP BY MAIN.XCODE

Total cost of each collection by institution

Query for total cost of each collection at 1 JUL 2017. Change date as required and replace XXX with your institution code. This query will total results by collection.

SELECT XCODE, SUM(PRC) FROM ITD

INNER JOIN INS ON ITD.IRN=INS.IRN

INNER JOIN ICO ON INS.IRN=ICO.IRN

INNER JOIN MAIN ON ICO.COL=MAIN.IRN

WHERE ITD.DTE < '1 JUL 2017'

AND MAIN.DEACT_DATE IS NULL

AND INS.CODE='XXX'

GROUP BY MAIN.XCODE

Average cost of each collection

SELECT XCODE, AVG(PRC) FROM ITD

INNER JOIN ICO ON ITD.IRN = ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

WHERE ITD.DTE < '1 NOV 2017'

AND MAIN.DEACT_DATE IS NULL

GROUP BY MAIN.XCODE

Average cost of each collection by institution

SQL query for the average age of the whole collection (provides an average creation date from which you should be able to calculate the average age). Replace XXX with your institution code.

SELECT CAST( AVG( CAST( DTE AS FLOAT )) AS DATETIME) FROM ITD

INNER JOIN INS ON ITD.IRN=INS.IRN

INNER JOIN MAIN ON INS.IRN=MAIN.IRN

WHERE MAIN.DEACT_DATE IS NULL
AND INS.CODE='XXX'

Highest priced item in each collection

Use this query to find anomalies if the above queries return unrealistic figures.

SELECT XCODE, MAX(PRC) FROM ITD

INNER JOIN ICO ON ITD.IRN = ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

AND MAIN.DEACT_DATE IS NULL

GROUP BY MAIN.XCODE

Average age of whole collection

SQL query for the average age of the whole collection (provides an average creation date from which you should be able to calculate the average age).

SELECT CAST( AVG( CAST( DTE AS FLOAT )) AS DATETIME)

FROM ITD

INNER JOIN MAIN ON ITD.IRN = MAIN.IRN

WHERE MAIN.DEACT_DATE IS NULL

Average age of specific collection

SQL query for the average age of a specific collection.

SELECT CAST( AVG( CAST( DTE AS FLOAT )) AS DATETIME)

FROM ITD

INNER JOIN ICO ON ITD.IRN = ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

AND MAIN.DEACT_DATE IS NULL WHERE MAIN.XCODE = 'AF'

Replace ‘AF’ with your collection code.

Average age of specific collection by institution

SELECT CAST( AVG( CAST( DTE AS FLOAT )) AS DATETIME) FROM ITD

INNER JOIN INS ON ITD.IRN=INS.IRN

INNER JOIN ICO ON INS.IRN=ICO.IRN

INNER JOIN MAIN ON ICO.COL=MAIN.IRN

WHERE MAIN.DEACT_DATE IS NULL

AND INS.CODE='XXX'

AND MAIN.XCODE='FIC'

Replace ‘XXX’ with your institution code and ‘FIC’ with your collection code.

Average age of each collection

SQL query for the average age of each collection (see above).

SELECT XCODE, COUNT(ITD.IRN) AS COUNT FROM ITD

INNER JOIN ICO ON ITD.IRN = ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

WHERE ITD.DTE < '1 NOV 2017'

AND MAIN.DEACT_DATE IS NULL

GROUP BY MAIN.XCODE

Average age of each collection by year

SQL query for the average age of each collection expressed as rounded year.

SELECT XCODE, CAST(GETDATE() - CAST( AVG( CAST( DTE AS FLOAT )) AS DATETIME) AS INT)/365 FROM ITD

INNER JOIN ICO ON ITD.IRN = ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

AND MAIN.DEACT_DATE IS NULL

GROUP BY MAIN.XCODE

Average age of each collection by institution

SELECT XCODE, CAST( AVG( CAST( DTE AS FLOAT )) AS DATETIME) FROM ITD

INNER JOIN INS ON ITD.IRN=INS.IRN

INNER JOIN ICO ON INS.IRN=ICO.IRN

INNER JOIN MAIN ON ICO.COL=MAIN.IRN

WHERE MAIN.DEACT_DATE IS NULL

AND INS.CODE='XXX'

GROUP BY MAIN.XCODE

Replace ‘XXX’ with your institution code.

Number of items in each collection

Count the number of items in each collection as at 1 Nov 2017. Change the date as required.

SELECT XCODE, COUNT(ITD.IRN) AS COUNT FROM ITD

INNER JOIN ICO ON ITD.IRN = ICO.IRN

INNER JOIN MAIN ON ICO.COL = MAIN.IRN

WHERE ITD.DTE < '1 NOV 2017'

AND MAIN.DEACT_DATE IS NULL

GROUP BY MAIN.XCODE

Number of items in each collection by institution

Count the number of items in each collection as at 1 Jul 2017. Change the date as required. Replace XXX with your institution code.

SELECT MAIN.XCODE, HEADING.HEAD, COUNT(ITD.IRN) AS COUNT FROM ITD

INNER JOIN INS ON ITD.IRN=INS.IRN

INNER JOIN ICO ON INS.IRN=ICO.IRN

INNER JOIN MAIN ON ICO.COL=MAIN.IRN

INNER JOIN HEADING ON MAIN.IRN=HEADING.IRN

WHERE ITD.DTE < '1 JUL 2017'

AND MAIN.DEACT_DATE IS NULL

AND INS.CODE='XXX'

GROUP BY MAIN.XCODE, HEADING.HEAD

Outstanding charges

Summary report of outstanding charges by charge type by borrower category.

SELECT HEADING.HEAD 'CATEGORY', CRG.TYP 'TYPE', SUM(CRG.DAMT)'TOTAL CHARGES' FROM CRG

INNER JOIN CHD ON CRG.IRN = CHD.IRN

INNER JOIN BDC ON CHD.BRW = BDC.IRN

INNER JOIN HEADING ON BDC.LNK = HEADING.IRN

WHERE CRG.CFLG = '0'

GROUP BY CRG.TYP, HEADING.HEAD

Outstanding charges by institution

SELECT HEADING.HEAD 'CATEGORY', CRG.TYP 'TYPE', SUM(CRG.DAMT)'TOTAL CHARGES' FROM CRG

INNER JOIN INS ON CRG.IRN=INS.IRN

INNER JOIN CHD ON CRG.IRN=CHD.IRN

INNER JOIN BDC ON CHD.BRW=BDC.IRN

INNER JOIN HEADING ON BDC.LNK=HEADING.IRN

WHERE CRG.CFLG = '0'

AND INS.CODE='XXX'

GROUP BY CRG.TYP, HEADING.HEAD

Replace ‘XXX’ with your institution code.

Value of items with a given status

The following SQL query will give the total value based on the Acquisition Price, Retail Price or (failing either of these) the average price (defaulting to $10 if this is null) for all items with a given status.

SELECT SUM(

CASE

WHEN PRC IS NOT NULL THEN PRC

WHEN RETPRC IS NOT NULL AND PRC IS NULL THEN RETPRC

ELSE ISNULL(AVP,10)

END)

FROM IST

JOIN ITD ON IST.IRN = ITD.IRN

JOIN ICO ON ITD.IRN = ICO.IRN

JOIN COD ON ICO.COL = COD.IRN

WHERE CDE = 'S'

Most commonly used dictionaries

Data dictionaries

XCODE	Retrieves all records by code (e.g. collection, location, user ID)
FORMAT	Retrieves all records in a given format e.g. FORMAT: BIB will retrieve all bibliographic records (with holdings)
CD	Created date – applies to all formats
LUPD	Last update date
DEACTDTE	Deactivation date (must have ‘Include deleted records’ ticked)
GMD	GMD
BFRMT	Bibliographic format (lookup table Y)
ITMLOCX	Retrieves items by location code
ITMCOLX	Retrieves items by collection code
ITMSCDE	Retrieves items by status code (lookup table KCS)
ITMDDTE	Item deactivation date (must have ‘Include deleted records’ ticked)
BDD	Retrieves BRWD records by date of birth
BRWCATX	Retrieves BRW records by borrower category code
BRWSEX	Retrieves BRW records by SEX
LOANCF	Loan complete flag (1 = complete (returned), 0 = incomplete (active))
ISSDTE	Date item issued
LOANSCDE	Loan status code (lookup table KCS)
DUEDTE	Item due date
RTNDTE	Date item returned
RSVPDTE	Reservation placement date
RSVCF	Reservation complete flag
RSVCTYP	Reservation complete type (lookup table ZCK)
RSVTLOC	Reservation pickup location
LICF	Line Item (Order) complete flag
LIOT	Line Item Order type
LIS	Line Item Status

Translating dictionaries

BIBITM	Bibliographic <-> Items
BIBRSV	Bibliographic <-> Reservations
BIBISS	Bibliographic <-> Issue
BIBLOAN	Bibliographic <-> Loan
BRWRSV	Borrower Account <-> Reservations
BDC	Borrower Category <-> Borrower
BRWDBRW	Borrower Account <-> Borrower Details
LOANITM	Loan <-> Item